const mysql = require("mysql");
const config = require("../config/default");
const e = require("express");

const db = mysql.createConnection({
  host: config.database.HOST,
  user: config.database.USER,
  password: config.database.PASSWORD,
});

// 连接指定的数据库
const pool = mysql.createPool({
  host: config.database.HOST,
  user: config.database.USER,
  password: config.database.PASSWORD,
  database: config.database.WALL,
});

// 直接使用pool.query
let bdbs = (sql, values) => {
  return new Promise((resolve, reject) => {
    db.query(sql, values, (err, result) => {
      if (err) {
        console.log("数据库操作失败：" + err.stack);
        reject(err);
      } else {
        console.log("数据库操作成功");
        resolve(result);
      }
    });
  });
};

let query = (sql, values) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        console.log("数据库连接失败：" + err.stack);
        reject(err);
      } else {
        connection.query(sql, values, (err, result) => {
          if (err) {
            console.log("数据库操作失败：" + err.stack);
            reject(err);
          } else {
            console.log("数据库操作成功");
            resolve(result);
          }
          connection.release(); // 释放连接
        });
      }
    });
  });
};

//创建数据库
let WALL = 'create database if not exists WALL CHARACTER SET utf8 COLLATE utf8_general_ci;';

let createDatabase = (db) => {
   return bdbs(db,[]);
};

//创建表
//留言照片表
let walls = `create table if not exists walls(
    id INT NOT NULL AUTO_INCREMENT,
    type INT NOT NULL COMMENT '类型0信息1图片',
    message VARCHAR(1000) COMMENT '留言',
    name VARCHAR(100) NOT NULL COMMENT '用户名',
    userId VARCHAR(100) NOT NULL COMMENT '创建者ID',
    moment VARCHAR(100) NOT NULL COMMENT '时间',
    label INT NOT NULL COMMENT '标签',
    color INT COMMENT '颜色',
    imgurl VARCHAR(100) COMMENT '图片路径',
    PRIMARY KEY( id )
);`;

// 留言的反馈
let feedbacks = `create table if not exists feedbacks(
  id INT NOT NULL AUTO_INCREMENT,
  wallId INT NOT NULL COMMENT '墙留言ID',
  userId VARCHAR(100) NOT NULL COMMENT '反馈者ID',
  type INT NOT NULL COMMENT '反馈类型 0喜欢 1举报 2撤销',
  moment VARCHAR(100) NOT NULL COMMENT '时间',
  PRIMARY KEY( id )
);`;

//留言评论
let comments = `create table if not exists comments(
    id INT NOT NULL AUTO_INCREMENT,
    wallId INT NOT NULL COMMENT '墙留言ID',
    userId VARCHAR(100) NOT NULL COMMENT '评论者ID',
    imgurl VARCHAR(100) COMMENT '头像路径',
    comment VARCHAR(1000) COMMENT '评论内容',
    name VARCHAR(100) NOT NULL COMMENT '用户名',
    moment VARCHAR(100) NOT NULL COMMENT '时间',
    PRIMARY KEY(id)
);`;

let createTable = (tableName) => {
  return query(tableName, []);
};

//先创建数据库，再创建表
async function create() {
   await createDatabase(WALL);
   createTable(walls);
   createTable(feedbacks);
   createTable(comments);
}
// create();

//新增walls
exports.insertWall = (values)=>{
    let _sql = 'insert into walls (type,message,name,userId,moment,label,color,imgurl) values (?,?,?,?,?,?,?,?);';
    return query(_sql,values);
}

//新建反馈
exports.insertFeedback=(value)=>{
  let _sql = `insert into 
  feedbacks (wallId,userId,type,moment) 
  values (?,?,?,?)`;
  return query(_sql,value)
}

//新建评论
exports.insertComment=(value)=>{
  let _sql = `insert into 
  comments (wallId,userId,imgurl,moment,comment,name) 
  values (?,?,?,?,?,?)`;
  return query(_sql,value)
}

//删除，连带删除子评论
exports.deletWall =(id)=>{
  let _sql = `delete a,b,c 
  from walls a
  left join feedbacks b on a.id=b.wallId 
  left join comments c on a.id =c.wallId 
  where a.id = "${id}"`;
  return query(_sql)
}

//删除反馈
exports.deletFeedback =(id)=>{
  let _sql = `delete from feedbacks where id = "${id}"`;
  return query(_sql)
}

//删除评论
exports.deletComment =(id)=>{
  let _sql = `delete from comments where id = "${id}"`;
  return query(_sql)
}

//倒序分页查询墙
exports.findWallPage = (page,pagesize,type,label)=>{
 let _sql;
 if(label == -1){
   _sql = `select * from walls where type = "${type}" order by id desc limit ${(page-1)*pagesize},${pagesize}`;
 }else{
   _sql = `select * from walls where type = "${type}" and label = "${label}" order by id desc limit ${(page-1)*pagesize},${pagesize}`;
 }
 console.log(query(_sql))
 return query(_sql) 

} 

//倒序分页查墙评论
exports.findCommentPage = (page,pagesize,id)=>{
  let _sql = `select * from comments where wallId = "${id}" order by id desc limit ${(page-1)*pagesize},${pagesize}`;
  return query(_sql)
}
 // 查询各反馈总数据
 exports.feedbackCount = (wid,type) => {
  let _sql = `select count(*) as count from feedbacks where wallId = "${wid}" and type = "${type}"`;
  return query(_sql)
 }

 //查询评论总数据
 exports.commentCount = (wid)=>{
  let _sql = `select count(*) as count from comments where wallId = "${wid}"`;
  return query(_sql)
 }

 //是否点赞
 exports.likeCount = (wid,uid)=>{
  let _sql = `select count(*) as count from feedbacks where wallId = "${wid}" and userId = "${uid}" and type = 0`;
  return query(_sql)
 }